IF OBJECT_ID('dbo.vwAgreementPrograms') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwAgreementPrograms
    IF OBJECT_ID('dbo.vwAgreementPrograms') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwAgreementPrograms >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwAgreementPrograms >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwAgreementPrograms
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/20/2007
--
-- Description: View to get ProgramLevel data, including Budget info.
-- Program Level Data, via dbo.vwAgreementPrograms (using dbo.AgrAgreementHierarchy)
-- View Returns Program data using Child FolderNumber
--
-- Used BY Procedure/Report: dbo.RptInvestigatorActivity()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/20/07     KC        Created
-- 8/22/07     KC        Finalized, used new BudgetAmounts, join
--                       using Heirarchy to join by FolderNumber (child)
-- 8/27/07     KC        Fixed Bug causing amounts to be off 
--                            (removed JOIN Criteria AFC.BudgetYear = AB.Year)
--                       Added RTRIMs to ProgramDirector
--                       Changed INNER JOIN on SUM Phrase to use core 
--                       AgrAgreement table
-- 9/6/07      KC        Changed for new version of AgrAgreement_Budget
--                       and to use AgrAgreement table instead of FACT table
-- 10/1/07     KC        Changed reference to AgrAgreement to AgrAgreement_Info
--
-- =======================================================================
CREATE VIEW dbo.vwAgreementPrograms
AS
SELECT    AH.FolderNumber,
          AP.ProgramFolderNumber,
          AP.ProgramShortTitle,
          AP.ProgramStartDate,
          AP.ProgramEndDate,
          AP.ProgramDirectorId AS ProgramDirectorId,
          dbo.fnGetPersonName(AP.ProgramDirectorId) + ISNULL(', ' + dbo.fnGetDegree(AP.ProgramDirectorId),'') AS ProgramDirector,
          AP.ProgramDirectCost,
          AP.ProgramIndirectCost
FROM      (SELECT   PROG.ProgramFolderNumber AS ProgramFolderNumber,
                    A.ShortTitle AS ProgramShortTitle,
                    A.ProjectStartDate AS ProgramStartDate,
                    A.ProjectEndDate AS ProgramEndDate,
                    A.PIPersonId AS ProgramDirectorId,
                    ProgramBudgetDirectCost AS ProgramDirectCost,
                    ProgramBudgetIndirectCost AS ProgramIndirectCost
          FROM
          (SELECT   AH.ParentFolderNumber AS ProgramFolderNumber, 
                    SUM(AB.BudgetDirectCost) AS ProgramBudgetDirectCost,
                    SUM(AB.BudgetIndirectCost) AS ProgramBudgetIndirectCost
          FROM  dbo.AgrAgreementHierarchy AH 
                    INNER JOIN dbo.AgrAgreement_Info AI ON AH.FolderNumber = AI.FolderNumber
                    INNER JOIN dbo.AgrAgreement_Budget AB ON AI.AgreementId = AB.AgreementId 
          GROUP BY AH.ParentFolderNumber) PROG INNER JOIN dbo.AgrAgreement A ON PROG.ProgramFolderNumber = A.FolderNumber) AP 

          JOIN dbo.AgrAgreementHierarchy AH ON AP.ProgramFolderNumber = AH.ParentFolderNumber
                              


go
IF OBJECT_ID('dbo.vwAgreementPrograms') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwAgreementPrograms >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwAgreementPrograms >>>'
go
